package com.xxcloud.common.utils.excel;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.xxcloud.common.utils.excel.ExportExcelPar;

public class ExcelUtils {

    public static final String NULL_STR = "NULL";
    public static final String BLANK_STR = "";

    public static void main(String[] args) {

    }

    public static <T> void transferToArrAndSetValue(HSSFWorkbook hssf, List<T> list, Class<T> clzz, ExcelTitleBean[] titles)
            throws IllegalArgumentException, IllegalAccessException {
        if (list == null || list.size() == 0) {
            return;
        }

        HSSFSheet sheet = hssf.getSheetAt(0);
        Field[] fields = clzz.getDeclaredFields();

        Map<String, Field> map = new HashMap<>();
        for (Field field : fields) {
            if (!"serialVersionUID".equalsIgnoreCase(field.getName())) {
                // 判断是否需要导出
                ExportExcelPar notpar = field.getAnnotation(ExportExcelPar.class);
                if (notpar == null || (notpar != null && notpar.ifExport())) {
                    // 导出
                    field.setAccessible(true);
                    map.put(field.getName(), field);
                }
            }
        }
        
        List<ExcelExportField> exports = new ArrayList<>();
        for(ExcelTitleBean b : titles) {
            Field field = map.get(b.getField());
            if(field != null) {
                exports.add(new ExcelExportField(b.getField(), field));
            }
        }

        for (int i = 0; i < list.size(); i++) {
            List<String> strings = new ArrayList<String>();
            for (ExcelExportField exportField : exports) {
                Field field = exportField.getField();
                
                if (!"serialVersionUID".equalsIgnoreCase(field.getName())) {
                    // 判断是否需要导出
                    ExportExcelPar notpar = field.getAnnotation(ExportExcelPar.class);
                    if (notpar == null || (notpar != null && notpar.ifExport())) {
                        // 导出
                        String str = "";
                        Object o = field.get(list.get(i));
                        if (notpar == null) {
                            str = (o == null ? BLANK_STR : o.toString());
                        } else {
                            if (o != null) {
                                // 前缀/ 后缀
                                if (!BLANK_STR.equals(notpar.prefix())) {
                                    str = notpar.prefix();
                                }
                                str += o.toString();
                                if (!BLANK_STR.equals(notpar.postfix())) {
                                    str = str + notpar.postfix();
                                }
                            } else {
                                str += (notpar.ifShowNull() ? NULL_STR : BLANK_STR);
                            }
                        }
                        strings.add(str);
                    }
                }
            }
            for (int j = 0; j < strings.size(); j++) {
                setValue(sheet, i, j, strings);
            }
        }

    }

    private static void setValue(HSSFSheet sheet, int i, int j, List<String> strings) {
        HSSFCell cellTemp = null;
        if (j == 0) {
            cellTemp = sheet.createRow(i + 1).createCell(j);
            cellTemp.setCellType(HSSFCell.CELL_TYPE_STRING);
            cellTemp.setCellValue(strings.get(j));
        } else {
            cellTemp = sheet.getRow(i + 1).createCell(j);
            cellTemp.setCellType(HSSFCell.CELL_TYPE_STRING);
            cellTemp.setCellValue(strings.get(j));
        }
    }

    /**
     * 导出excel
     * 
     * @param response
     * @param arr 表头列表
     * @param list 数据数组
     * @param clzz 数据对象类型
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> void export(HttpServletResponse response, ExcelTitleBean[] arr, List<T> list, Class<T> clzz)
            throws IllegalArgumentException, IllegalAccessException {
        HSSFWorkbook hssf = writeExcelFirst(arr, true);
        transferToArrAndSetValue(hssf, list, clzz, arr);
        responseXLS("" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()), response, hssf);
    }

    /**
     * 获取 表格hssf
     *
     * @param arr
     * @param list
     * @param clzz
     * @param <T>
     * @return
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> HSSFWorkbook getHssf(ExcelTitleBean[] arr, List<T> list, Class<T> clzz)
            throws IllegalArgumentException, IllegalAccessException {
        HSSFWorkbook hssf = writeExcelFirst(arr, true);
        transferToArrAndSetValue(hssf, list, clzz, arr);
        return hssf;
    }

    /**
     * 导出excel
     * 
     * @param response
     * @param xlsName excel名字前缀
     * @param arr 表头列表
     * @param list 数据数组
     * @param clzz 数据对象类型
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> void export(HttpServletResponse response, String xlsName, ExcelTitleBean[] titles, List<T> list,
            Class<T> clzz) throws IllegalArgumentException, IllegalAccessException {
        HSSFWorkbook hssf = writeExcelFirst(titles, true);
        transferToArrAndSetValue(hssf, list, clzz, titles);
        if (StringUtils.isBlank(xlsName)) {
            xlsName = "";
        }
        responseXLS(xlsName + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()), response, hssf);
    }

    /**
     * true为写一行标题 false为写一列标题
     * 
     * @param arr
     * @param ifRow
     * @return
     */
    private static HSSFWorkbook writeExcelFirst(ExcelTitleBean[] titles, boolean ifRow) {
        HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
        HSSFSheet sheet = workbook.createSheet(); // 产生工作表对象
        workbook.setSheetName(0, "default");
        sheet.setColumnWidth(0, 20 * 256);
        int size = titles.length;
        for (int i = 0; i < size; i++) {
            if (ifRow) {
                HSSFCell cellTemp = null;
                if (i == 0) {
                    cellTemp = sheet.createRow(0).createCell(i);
                } else {
                    cellTemp = sheet.getRow(0).createCell(i);
                }
                cellTemp.setCellType(HSSFCell.CELL_TYPE_STRING);
                cellTemp.setCellValue(titles[i].getTitle());
            } else {
                HSSFCell cellTemp = sheet.createRow(i).createCell(0);
                cellTemp.setCellType(HSSFCell.CELL_TYPE_STRING);
                cellTemp.setCellValue(titles[i].getTitle());
            }

        }
        return workbook;
    }

    public static void responseXLS(String xlsName, HttpServletResponse response, HSSFWorkbook workbook) {
        OutputStream os = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("content-disposition", "attachment;filename=" + xlsName + ".xls");
            // 写入到 客户端response
            os = response.getOutputStream();
            workbook.write(os);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if(os != null) {
                    os.flush();
                    os.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

    public static HSSFCellStyle getHeaderStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 表头设置为居中显示
        style.setFillForegroundColor((short) 15);// 设置背景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        return style;
    }

    /**
     * 导出csv
     * 
     * @param response
     * @param arr 表头列表
     * @param list 数据数组
     * @param clzz 数据对象类型
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> void exportCSV(HttpServletResponse response, ExcelTitleBean[] arr, List<T> list, Class<T> clzz)
            throws IllegalArgumentException, IllegalAccessException {
        HSSFWorkbook hssf = writeExcelFirst(arr, true);
        transferToArrAndSetValue(hssf, list, clzz, arr);
        responseCSV("" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()), response, hssf);
    }

    /**
     * 导出csv
     * 
     * @param response
     * @param xlsName csv名字前缀
     * @param arr 表头列表
     * @param list 数据数组
     * @param clzz 数据对象类型
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static <T> void exportCSV(HttpServletResponse response, String xlsName, ExcelTitleBean[] arr, List<T> list,
            Class<T> clzz) throws IllegalArgumentException, IllegalAccessException {
        HSSFWorkbook hssf = writeExcelFirst(arr, true);
        transferToArrAndSetValue(hssf, list, clzz, arr);
        if (StringUtils.isBlank(xlsName)) {
            xlsName = "";
        }
        responseCSV(xlsName + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()), response, hssf);
    }

    public static void responseCSV(String xlsName, HttpServletResponse response, HSSFWorkbook workbook) {
        OutputStream os = null;
        try {
            response.setContentType("application/csv");
            response.setHeader("content-disposition", "attachment;filename=" + xlsName + ".csv");
            // 写入到 客户端response
            os = response.getOutputStream();
            workbook.write(os);

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (os != null) {
                    os.flush();
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }
    

}
